package com.et114.modules.commons;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/** */
/**
 * 生成导出Excel文件对象
 * 
 * @author John.Zhu
 * 
 */
public class XLSExport {
	
	// 设置cell编码解决中文高位字节截断
	private static short	XLS_ENCODING	= HSSFWorkbook.ENCODING_UTF_16;
	
	// 定制日期格式
	private static String	DATE_FORMAT		= "m/d/yy";					// "m/d/yy
																		// h:mm"
																			
	// 定制浮点数格式
	private static String	NUMBER_FORMAT	= " #,##0.00 ";
	
	private String			xlsFileName;
	
	private HSSFWorkbook	workbook;
	
	private HSSFSheet		sheet;
	
	private HSSFRow			row;
	
	/** */
	/**
	 * 初始化Excel
	 * 
	 * @param fileName
	 *            导出文件名
	 */
	public XLSExport ( String fileName ) {
		this.xlsFileName = fileName;
		this.workbook = new HSSFWorkbook ( );
		this.sheet = workbook.createSheet ( );
		 workbook.setSheetName (0, "custdata", HSSFWorkbook.ENCODING_UTF_16);
	}
	
	/** */
	/**
	 * 导出Excel文件
	 * 
	 * @throws Exception
	 */
	public void exportXLS ( ) throws Exception {
		try {
			FileOutputStream fOut = new FileOutputStream ( xlsFileName );
			workbook.write ( fOut );
			fOut.flush ( );
			fOut.close ( );
		} catch ( FileNotFoundException e ) {
			throw new Exception ( " 生成导出Excel文件出错! " , e );
		} catch ( IOException e ) {
			throw new Exception ( " 写入Excel文件出错! " , e );
		}
		
	}
	
	public void exportXLS ( HttpServletResponse response ) throws Exception {
		OutputStream out = response.getOutputStream ( );
		try {
			 
			response.setContentType( "application/vnd.ms-excel;charset=UTF-8"); 
			response.setCharacterEncoding( "UTF-8");   
			response.setHeader( "Content-Disposition" , "attachment;filename="  + new String ( xlsFileName.getBytes ( "gb2312" ) , "iso8859-1" ) );//设置文件头编码方式和文件名
			
			workbook.write ( out );
			
		} catch ( FileNotFoundException e ) {
			throw new Exception ( " 生成导出Excel文件出错! " , e );
		} catch ( IOException e ) {
			e.printStackTrace ( );
			throw new Exception ( " 写入Excel文件出错! " , e );
			
		}finally{
			out.close ( );
			response.flushBuffer ( );
		}
		
	}
	
	/** */
	/**
	 * 增加一行
	 * 
	 * @param index
	 *            行号
	 */
	public void createRow ( int index ) {
		this.row = this.sheet.createRow ( index );
	}
	
	/** */
	public void setCell ( int index , String value ,String style) {
		HSSFCellStyle cellStyle = workbook.createCellStyle ( ); // 建立新的cell样式
		HSSFDataFormat format = workbook.createDataFormat ( ); 
		HSSFFont hf=workbook.createFont ( );
		if("c".equalsIgnoreCase ( style )){
			hf.setColor ( HSSFFont.COLOR_RED );
			cellStyle.setFont ( hf );
		}else if("b".equalsIgnoreCase ( style )){
			hf.setBoldweight ( HSSFFont.BOLDWEIGHT_BOLD );
			cellStyle.setFont ( hf );
		}
		
		HSSFCell cell = this.row.createCell ( ( short ) index );
		cell.setCellType ( HSSFCell.CELL_TYPE_STRING );
		cell.setEncoding ( XLS_ENCODING );
		cell.setCellStyle ( cellStyle );
		cell.setCellValue ( value );
	}
	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell ( int index , String value ) {
		HSSFCell cell = this.row.createCell ( ( short ) index );
		cell.setCellType ( HSSFCell.CELL_TYPE_STRING );
		cell.setEncoding ( XLS_ENCODING );
		cell.setCellValue ( value );
	}
	
	/** */
	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell ( int index , Calendar value ) {
		HSSFCell cell = this.row.createCell ( ( short ) index );
		cell.setEncoding ( XLS_ENCODING );
		cell.setCellValue ( value.getTime ( ) );
		HSSFCellStyle cellStyle = workbook.createCellStyle ( ); // 建立新的cell样式
		cellStyle.setDataFormat ( HSSFDataFormat
				.getBuiltinFormat ( DATE_FORMAT ) ); // 设置cell样式为定制的日期格式
		cell.setCellStyle ( cellStyle ); // 设置该cell日期的显示格式
	}
	
	/** */
	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell ( int index , int value ) {
		HSSFCell cell = this.row.createCell ( ( short ) index );
		cell.setCellType ( HSSFCell.CELL_TYPE_NUMERIC );
		cell.setCellValue ( value );
	}
	
	/** */
	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell ( int index , double value ) {
		HSSFCell cell = this.row.createCell ( ( short ) index );
		cell.setCellType ( HSSFCell.CELL_TYPE_NUMERIC );
		cell.setCellValue ( value );
		HSSFCellStyle cellStyle = workbook.createCellStyle ( ); // 建立新的cell样式
		HSSFDataFormat format = workbook.createDataFormat ( );
		cellStyle.setDataFormat ( format.getFormat ( NUMBER_FORMAT ) ); // 设置cell样式为定制的浮点数格式
		cell.setCellStyle ( cellStyle ); // 设置该cell浮点数的显示格式
	}
	
}
